package gov.nhso.prov.acc.apps.acc7;

import java.io.OutputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings({"rawtypes","unchecked","deprecation"})
public class ACC70401 extends Utility{
	public String url = "/apps/acc7/ACC70401A.jsp";

	public ACC70401(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		
		System.out.println("File name > ACC70401" );
		System.out.println("Process name >  search");
		System.out.println("Job > " + job);
		System.out.println("Line > 37");
		
		if(job.equals("init")){
			this.init(request);
		} else if(job.equals("search")){
			this.search(request);
		} else if(job.equals("export")){
			this.export(request, response);
		}
	}
	
	private String init(HttpServletRequest request) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("job", "search");
			conn.commit();
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String search(HttpServletRequest request) {
		
		Connection conn = null;
		ISUD dao = null;
		
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String month = this.getAttr(request, "month");
			StringBuffer sb = new StringBuffer();
			Map map = new HashMap();
			Map bufferMap = new HashMap();
			List list = new ArrayList();
			List bufferList = new ArrayList();
			List sumIncomeAmountList = new ArrayList();
			List sumIncomeAmountSAPList = new ArrayList();
			int iYear = Integer.parseInt(budgetYear) - 543;
			int sumCount = 0;
			String provTxt = "";

			if (provinceId.equals("")) {
				if (user(request).getAreaLevel().equals("2")) {
					provTxt = " IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '"+user(request).getAreaCode()+"') ";
				} else {
					provTxt = " IS NOT NULL";
				}
			} else {
				provTxt = " = '"+provinceId+"' ";
			}
			
			sb.append("SELECT NVL(ACT.INCOME_AMOUNT,0) AS SUM_INCOME , ACT.INCOME_DATE,ACI.INCOME_DESC,ACI.DOC_NO,ACI.REF_NO,ACT.BUDGET_YEAR ");
			sb.append("FROM ACC7_TRANSACTION ACT,ACC7_INCOME ACI ");
			sb.append("WHERE (ACT.TRANSACTION_TYPE = 'I' OR ACT.TRANSACTION_TYPE = 'B') AND ACT.PROVINCE_ID "+provTxt+" ");
			sb.append("AND ACT.TRANSACTION_TIMESTAMP = ACI.TRANSACTION_TIMESTAMP AND TO_CHAR(ACT.INCOME_DATE,'YYYYMM') = '"+iYear+month+"' ");
			sb.append("ORDER BY ACT.INCOME_DATE ASC , ACI.DOC_NO ASC ");
			sumIncomeAmountList = dao.listDataSql(sb.toString());
			request.setAttribute("sumIncomeAmountList", sumIncomeAmountList);
			
			System.out.println(" SQL 1 > " + sb.toString());
			
			sb.delete(0, sb.length());
			sb.append("SELECT NVL(INCOME_AMOUNT,0) AS SUM_INCOME , INCOME_DATE , REF_NO , BUDGET_YEAR ");
			sb.append("FROM ACC7_TRANSACTION ");
			sb.append("WHERE TRANSACTION_TYPE = 'S' AND PROVINCE_ID "+provTxt+" AND TO_CHAR(INCOME_DATE,'YYYYMM') = '"+iYear+month+"' ORDER BY INCOME_DATE ASC ");
			sumIncomeAmountSAPList = dao.listDataSql(sb.toString());
			request.setAttribute("sumIncomeAmountSAPList", sumIncomeAmountSAPList);
			
			System.out.println(" SQL 2 > " + sb.toString());
			
			sb.delete(0, sb.length());
			sb.append("SELECT CATEGORY_NAME , CATEGORY_ID ");
			sb.append("FROM ACC67_EXPENSE_CATEGORY ");
			sb.append("WHERE CATEGORY_ACC = '7' AND CATEGORY_LEVEL = '1' ORDER BY CATEGORY_SEQ ASC");
			list = dao.listDataSql(sb.toString());
			
			if(list.size()>0){
				for(int i = 0;i<list.size();i++){
					map = (Map)list.get(i);
					sb.delete(0, sb.length());
					sb.append("SELECT COUNT(CATEGORY_PARENT) AS COUNT_CATEGORY_PARENT ");
					sb.append("FROM ACC67_EXPENSE_CATEGORY ");
					sb.append("WHERE CATEGORY_ACC = '7' AND CATEGORY_PARENT = '"+map.get("CATEGORY_ID").toString()+"' AND CATEGORY_APPLY ='Y' ");
					bufferMap = dao.findDataSql(sb.toString());
					map.put("COUNT_CATEGORY_PARENT", bufferMap.get("COUNT_CATEGORY_PARENT"));
					bufferList.add(map);
					if (!bufferMap.get("COUNT_CATEGORY_PARENT").equals("0")) {
						sumCount += Integer.parseInt(bufferMap.get("COUNT_CATEGORY_PARENT").toString());
					} else {
						sumCount += 1;
					}
				}
			}
			request.setAttribute("sumCount", sumCount);
			request.setAttribute("categoryLvl1", bufferList);
			/*int iMonth = Integer.parseInt(month);
			if(iMonth == 1){
				month = "12";
				iYear = iYear - 1;
			} else if (iMonth >= 10){
				iYear = iYear - 1;
				iMonth = iMonth-1;
				month = String.valueOf(iMonth);
			} else {
				iMonth = iMonth-1;
				month = String.valueOf(iMonth);
			}

			if (month.length() == 1){
				month = "0"+month;
			}*/
			sb.delete(0, sb.length());
			
			sb.append("SELECT NVL(SUM(INCOME_AMOUNT),0) - NVL(SUM(EXPENSE_AMOUNT),0) AS RESULTS ");
			sb.append("FROM ACC7_TRANSACTION ");
			sb.append("WHERE PROVINCE_ID " + provTxt + " ");
			sb.append("AND (TO_CHAR(EXPENSE_DATE,'YYYYMM') < '" + iYear + "" + month+"' OR TO_CHAR(INCOME_DATE,'YYYYMM') < '"+iYear+""+month+"')");
			
			map = dao.findDataSql(sb.toString());
			request.setAttribute("result", map.get("RESULTS"));//ยอดรวม
			
			month = this.getAttr(request, "month");
			
			sb.delete(0, sb.length());
			sb.append("SELECT NVL(EXPENSE_DATE,'') AS EXPENSE_DATE,REF_NO,DOC_NO,EXPENSE_DESC, 0 AS EXPENSE_AMOUNT,BUDGET_YEAR ");
			sb.append("FROM ACC7_EXPENSE ");
			sb.append("WHERE PROVINCE_ID "+provTxt+" AND ACTIVE = 'Y' AND EXPENSE_STATUS = 'Y' ");
			sb.append("AND TO_CHAR(EXPENSE_DATE,'YYYYMM') = '"+iYear+month+"' ");
			sb.append("ORDER BY EXPENSE_DATE ASC ");
			
			list = dao.listDataSql(sb.toString());
			
			request.setAttribute("listData", list);
			
			System.out.println("SQL 3 > " + sb.toString());

//			if (list.size() == 0 && sumIncomeAmountList.size() == 0 && sumIncomeAmountSAPList.size() == 0){
//				this.message(request).setMsgCode("I001");
//			}
	
			request.setAttribute("month", this.getAttr(request, "month"));
			request.setAttribute("monthName", this.properties().month().get(month));
			request.setAttribute("provinceName", this.dataUtil(dao).getProvinceName(provinceId));
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("budgetYear", budgetYear);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("job", "search");
			
			conn.commit();
			
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String export(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			int i = 0;
			int j = 0;
			int rowInt = 5;
			int bufferInt = 1;
			int iChild = 1;
			int lastCell = 1;
			short s = 0;
			StringBuffer sb = new StringBuffer();
			Map map = new HashMap();
			Map bufferMap = new HashMap();
			List list = new ArrayList();
			List bufferList = new ArrayList();
			List listCategory = new ArrayList();
			List sumIncomeAmountList = new ArrayList();
			List sumIncomeAmountSAPList = new ArrayList();
			
			String provinceId = this.getAttr(request, "provinceId");
			String budgetYear = this.getAttr(request, "budgetYear");
			String month = this.getAttr(request, "month");
			String provTxt = "";
			int iYear = Integer.parseInt(budgetYear) - 543;

			if (provinceId.equals("")) {
				if (user(request).getAreaLevel().equals("2")) {
					provTxt = " IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '"+user(request).getAreaCode()+"') ";
				} else {
					provTxt = " IS NOT NULL";
				}
			} else {
				provTxt = " = '"+provinceId+"' ";
			}
			response.setContentType("text/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=\""+provinceId+"-"+month+"-"+budgetYear+".xls\"");
			response.setCharacterEncoding("windows-874");
			
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet(provinceId+"-"+month+"-"+budgetYear);
			
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			sheet.setDefaultColumnWidth((short) 20);
			HSSFRow row = sheet.createRow((short)0);
			HSSFRow rowChild = sheet.createRow((short)4);
			HSSFCell headerCell = row.createCell(0);
			
			headerCell.setCellStyle(cellStyle);
			headerCell.setCellValue("สำนักงานสาธารณสุขจังหวัด " + this.dataUtil(dao).getProvinceName(provinceId) + "\r\n ทะเบียนคุมค่าบริหารจัดการ  \r\n ประจำเดือน "+this.properties().month().get(month)+ " " + budgetYear);
			
			row = sheet.createRow((short)3);
			HSSFCell cell1 = row.createCell(0);
			HSSFCell cell2 = row.createCell(1);
			HSSFCell cell3 = row.createCell(2);
			HSSFCell cell4 = row.createCell(3);
			HSSFCell cell5 = row.createCell(4);

			cell1.setCellStyle(cellStyle);
			cell2.setCellStyle(cellStyle);
			cell3.setCellStyle(cellStyle);
			cell4.setCellStyle(cellStyle);
			cell5.setCellStyle(cellStyle);
			cell1.setCellValue("วัน เดือน ปี");
			cell2.setCellValue("เลขที่เอกสารอ้างอิง");
			cell3.setCellValue("ปีงบประมาณ");
			cell4.setCellValue("รายการ");
			cell5.setCellValue("รายรับ");
			sheet.addMergedRegion(new CellRangeAddress(3, 4, 0, 0));
			sheet.addMergedRegion(new CellRangeAddress(3, 4, 1, 1));
			sheet.addMergedRegion(new CellRangeAddress(3, 4, 2, 2));
			sheet.addMergedRegion(new CellRangeAddress(3, 4, 3, 3));
			sheet.addMergedRegion(new CellRangeAddress(3, 4, 4, 4));

			sb.append("SELECT CATEGORY_NAME,CATEGORY_ID ");
			sb.append("FROM ACC67_EXPENSE_CATEGORY ");
			sb.append("WHERE CATEGORY_ACC = '7' AND CATEGORY_LEVEL = '1' ORDER BY CATEGORY_SEQ");
			list = dao.listDataSql(sb.toString());
			if(list.size()>0){
				for(i=0;i<list.size();i++){
					map = (Map)list.get(i);
					sb.delete(0, sb.length());
					sb.append("SELECT COUNT(CATEGORY_PARENT) AS COUNT_CATEGORY_PARENT ");
					sb.append("FROM ACC67_EXPENSE_CATEGORY ");
					sb.append("WHERE CATEGORY_ACC = '7' AND CATEGORY_PARENT = '"+map.get("CATEGORY_ID").toString()+"' ");
					bufferMap = dao.findDataSql(sb.toString());
					HSSFCell cell = row.createCell(i+4+bufferInt);
					cell.setCellStyle(cellStyle);
					cell.setCellValue(map.get("CATEGORY_NAME").toString());
					
					if (Integer.parseInt(bufferMap.get("COUNT_CATEGORY_PARENT").toString()) > 0){
						sheet.addMergedRegion(new CellRangeAddress(3, 3, rowInt, rowInt+Integer.parseInt(bufferMap.get("COUNT_CATEGORY_PARENT").toString())-1));
						rowInt += Integer.parseInt(bufferMap.get("COUNT_CATEGORY_PARENT").toString());
						bufferInt += Integer.parseInt(bufferMap.get("COUNT_CATEGORY_PARENT").toString())-1;

						sb.delete(0, sb.length());
						sb.append("SELECT CATEGORY_NAME,CATEGORY_ID ");
						sb.append("FROM ACC67_EXPENSE_CATEGORY ");
						sb.append("WHERE CATEGORY_ACC = '7' AND CATEGORY_PARENT = '"+map.get("CATEGORY_ID").toString()+"' AND CATEGORY_APPLY ='Y' ORDER BY CATEGORY_SEQ ");
						bufferList = dao.listDataSql(sb.toString());
						for (j=0;j<bufferList.size();j++){
							bufferMap = (Map)bufferList.get(j);
							HSSFCell chidCell = rowChild.createCell(iChild+4);
							chidCell.setCellStyle(cellStyle);
							chidCell.setCellValue(bufferMap.get("CATEGORY_NAME").toString());
							listCategory.add(bufferMap.get("CATEGORY_ID").toString());
							iChild++;
						}
						
					} else {
						listCategory.add(map.get("CATEGORY_ID").toString());
						sheet.addMergedRegion(new CellRangeAddress(3, 4, i+5+bufferInt, i+5+bufferInt));
						rowInt += 1;
						iChild++;
					}
				}
			}
			HSSFCellStyle cellAlignRigth = wb.createCellStyle();
			cellAlignRigth.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			lastCell = iChild+4;
			HSSFCell cell6 = row.createCell(lastCell);
			HSSFCell cell7 = row.createCell(lastCell+1);
			cell6.setCellStyle(cellStyle);
			cell7.setCellStyle(cellStyle);
			cell6.setCellValue("จ่ายรวม");
			cell7.setCellValue("คงเหลือ");
			sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, lastCell+1));
			sheet.addMergedRegion(new CellRangeAddress(3, 4, lastCell, lastCell));
			sheet.addMergedRegion(new CellRangeAddress(3, 4, lastCell+1, lastCell+1));
			bufferInt = 5;
			double remain = 0d;
			HSSFCell cellDetail1 = null;
			HSSFCell cellDetail2 = null;
			HSSFCell cellDetail3 = null;
			HSSFCell cellDetail7 = null;
			HSSFCell cellDetail8 = null;
			HSSFCell cellDetail9 = null;
			HSSFCell cellDetail10 = null;
			
		/*	int iMonth = Integer.parseInt(month);
			if(iMonth == 1){
				month = "12";
				iYear = iYear - 1;
			} else if (iMonth >= 10){
				iYear = iYear - 1;
				iMonth = iMonth-1;
				month = String.valueOf(iMonth);
			} else {
				iMonth = iMonth-1;
				month = String.valueOf(iMonth);
			}

			if (month.length() == 1){
				month = "0"+month;
			}*/
			sb.delete(0, sb.length());
			sb.append("SELECT NVL(SUM(INCOME_AMOUNT),0) - NVL(SUM(EXPENSE_AMOUNT),0) AS RESULTS ");
			sb.append("FROM ACC7_TRANSACTION ");
			sb.append("WHERE PROVINCE_ID "+provTxt+" ");
			sb.append("AND (TO_CHAR(EXPENSE_DATE,'YYYYMM') < '"+iYear+""+month+"' OR TO_CHAR(INCOME_DATE,'YYYYMM') < '"+iYear+""+month+"')");
			map = dao.findDataSql(sb.toString());//ยอดยกมา				
			row = sheet.createRow((short)bufferInt);
			cellDetail1 = row.createCell(3);
			cellDetail2 = row.createCell(lastCell);
			cellDetail3 = row.createCell(lastCell+1);
			cellDetail7 = row.createCell(0);
			cellDetail8 = row.createCell(1);
			cellDetail9 = row.createCell(4);
			cellDetail10 = row.createCell(2);
			cellDetail2.setCellStyle(cellAlignRigth);
			cellDetail3.setCellStyle(cellAlignRigth);
			cellDetail9.setCellStyle(cellAlignRigth);
			cellDetail7.setCellValue("");
			cellDetail8.setCellValue("");
			cellDetail10.setCellValue("");
			cellDetail1.setCellValue("ยอดคงเหลือ");
			cellDetail9.setCellValue(this.digitUtil().formatDecimal(Double.parseDouble(map.get("RESULTS").toString()), 2));
			cellDetail2.setCellValue("0.00");
			cellDetail3.setCellValue(this.digitUtil().formatDecimal(Double.parseDouble(map.get("RESULTS").toString()), 2));
			bufferInt++;
			remain = remain + Double.parseDouble(map.get("RESULTS").toString());
			month = this.getAttr(request, "month");
			sb.delete(0, sb.length());
			sb.append("SELECT NVL(INCOME_AMOUNT,0) AS SUM_INCOME,INCOME_DATE,REF_NO,BUDGET_YEAR ");
			sb.append("FROM ACC7_TRANSACTION ");
			sb.append("WHERE TRANSACTION_TYPE = 'S' AND PROVINCE_ID "+provTxt+" AND TO_CHAR(INCOME_DATE,'YYYYMM') = '"+iYear+month+"' ORDER BY INCOME_DATE ASC ");
			sumIncomeAmountSAPList = dao.listDataSql(sb.toString());

			for(i=0;i<sumIncomeAmountSAPList.size();i++){
				map = (Map) sumIncomeAmountSAPList.get(i);				
				row = sheet.createRow((short)bufferInt);
				cellDetail1 = row.createCell(3);
				cellDetail2 = row.createCell(lastCell);
				cellDetail3 = row.createCell(lastCell+1);
				cellDetail7 = row.createCell(0);
				cellDetail8 = row.createCell(1);
				cellDetail10 = row.createCell(2);
				cellDetail9 = row.createCell(4);
				cellDetail2.setCellStyle(cellAlignRigth);
				cellDetail3.setCellStyle(cellAlignRigth);
				cellDetail9.setCellStyle(cellAlignRigth);
				cellDetail7.setCellValue(this.dateUtil().changeDateFromDB(dao.chkData(map.get("INCOME_DATE")),"/"));
				cellDetail8.setCellValue(dao.chkData(map.get("REF_NO")));
				cellDetail10.setCellValue(dao.chkData(map.get("BUDGET_YEAR")));
				cellDetail1.setCellValue("รายการจาก SAP");
				cellDetail9.setCellValue(this.digitUtil().formatDecimal(Double.parseDouble(map.get("SUM_INCOME").toString()), 2));
				cellDetail2.setCellValue("0.00");
				cellDetail3.setCellValue(this.digitUtil().formatDecimal(remain + Double.parseDouble(map.get("SUM_INCOME").toString()), 2));
				remain = remain + Double.parseDouble(map.get("SUM_INCOME").toString());
				bufferInt++;
			}
			

			sb.delete(0, sb.length());
			sb.append("SELECT NVL(ACT.INCOME_AMOUNT,0) AS SUM_INCOME,ACT.INCOME_DATE,ACI.INCOME_DESC,ACI.DOC_NO,ACT.BUDGET_YEAR,ACI.REF_NO ");
			sb.append("FROM ACC7_TRANSACTION ACT,ACC7_INCOME ACI ");
			sb.append("WHERE (ACT.TRANSACTION_TYPE = 'I' OR ACT.TRANSACTION_TYPE = 'B') AND ACT.PROVINCE_ID "+provTxt+" ");
			sb.append("AND ACT.TRANSACTION_TIMESTAMP = ACI.TRANSACTION_TIMESTAMP AND TO_CHAR(ACT.INCOME_DATE,'YYYYMM') = '"+iYear+month+"' ");
			sb.append("ORDER BY ACT.INCOME_DATE ASC ");
			sumIncomeAmountList = dao.listDataSql(sb.toString());
			
			for(i=0;i<sumIncomeAmountList.size();i++){
				map = (Map) sumIncomeAmountList.get(i);		
				row = sheet.createRow((short)bufferInt);
				cellDetail1 = row.createCell(3);
				cellDetail2 = row.createCell(lastCell);
				cellDetail3 = row.createCell(lastCell+1);
				cellDetail7 = row.createCell(0);
				cellDetail8 = row.createCell(1);
				cellDetail10 = row.createCell(2);
				cellDetail9 = row.createCell(4);

				cellDetail2.setCellStyle(cellAlignRigth);
				cellDetail3.setCellStyle(cellAlignRigth);
				cellDetail9.setCellStyle(cellAlignRigth);
				
				cellDetail7.setCellValue(this.dateUtil().changeDateFromDB(dao.chkData(map.get("INCOME_DATE")),"/"));
				cellDetail8.setCellValue(dao.chkData(map.get("REF_NO")));
				cellDetail10.setCellValue(dao.chkData(map.get("BUDGET_YEAR")));
				cellDetail1.setCellValue(dao.chkData(map.get("INCOME_DESC")));
				cellDetail9.setCellValue(this.digitUtil().formatDecimal(Double.parseDouble(map.get("SUM_INCOME").toString()), 2));
				cellDetail2.setCellValue("0.00");
				cellDetail3.setCellValue(this.digitUtil().formatDecimal(remain + Double.parseDouble(map.get("SUM_INCOME").toString()), 2));
				remain = remain + Double.parseDouble(map.get("SUM_INCOME").toString());
				bufferInt++;
			}


			sb.delete(0, sb.length());
			sb.append("SELECT NVL(EXPENSE_DATE,'') AS EXPENSE_DATE,REF_NO,DOC_NO,EXPENSE_DESC, 0 AS EXPENSE_AMOUNT,BUDGET_YEAR ");
			sb.append("FROM ACC7_EXPENSE ");
			sb.append("WHERE PROVINCE_ID "+provTxt+" AND ACTIVE = 'Y' AND EXPENSE_STATUS = 'Y'  ");
			sb.append("AND TO_CHAR(EXPENSE_DATE,'YYYYMM') = '"+iYear+month+"' ");
			sb.append("ORDER BY EXPENSE_DATE ASC ");
			list = dao.listDataSql(sb.toString());
			
			HSSFCell cellDetail4 = null;
			HSSFCell cellDetail5 = null;
			HSSFCell cellDetail6 = null;
			double sum = 0d;
			double sum2 = 0d;
			for(i=0;i<list.size();i++){
				map = (Map) list.get(i);
				row = sheet.createRow((short)bufferInt+s);
				rowInt = 5;
				cellDetail1 = row.createCell(0);
				cellDetail2 = row.createCell(1);
				cellDetail10 = row.createCell(2);
				cellDetail3 = row.createCell(3);
				cellDetail9 = row.createCell(4);
				cellDetail9.setCellStyle(cellAlignRigth);
				cellDetail1.setCellValue(this.dateUtil().changeDateFromDB(map.get("EXPENSE_DATE").toString(), "/"));
				cellDetail2.setCellValue(map.get("REF_NO").toString());
				cellDetail10.setCellValue(map.get("BUDGET_YEAR").toString());
				cellDetail3.setCellValue(map.get("EXPENSE_DESC").toString());
				cellDetail9.setCellValue("0.00");
				for(j=0;j<listCategory.size();j++){
					cellDetail4 = row.createCell(rowInt);
					sum = Double.parseDouble(this.dataUtil(dao).sumAmountByDocNo(map.get("DOC_NO").toString(), listCategory.get(j).toString()));
					if (sum == 0){
						cellDetail4.setCellValue("");
					} else {
						cellDetail4.setCellValue(this.digitUtil().formatDecimal(sum, 2));
					}
					cellDetail4.setCellStyle(cellAlignRigth);
					rowInt++;
					sum2 += sum;
					sum = 0;
				}
				cellDetail5 = row.createCell(rowInt);
				cellDetail5.setCellValue(this.digitUtil().formatDecimal(sum2, 2));
				cellDetail6 = row.createCell(rowInt+1);
				cellDetail6.setCellValue(this.digitUtil().formatDecimal(remain - sum2, 2));			
				cellDetail5.setCellStyle(cellAlignRigth);
				cellDetail6.setCellStyle(cellAlignRigth);
				s++;
				remain = remain - sum2;
				sum2 = 0;
			}
			
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
}
